
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[Users_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[Users_Insert]

IF OBJECT_ID(N'[dbo].[Users_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[Users_Update]

IF OBJECT_ID(N'[dbo].[Users_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[Users_Delete]

IF OBJECT_ID(N'[dbo].[Users_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[Users_Select]

--endregion

GO


--region [dbo].[Users_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Users_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdUser int = null,
	@UserName nvarchar(100) = null,
	@Password nvarchar(100) = null,
	@Email nvarchar(100) = null,
	@Fullname nvarchar(200) = null,
	@Birthdate datetime = null,
	@Phone nvarchar(20) = null,
	@IdCountry int = null,
	@Province nvarchar(100) = null,
	@Address nvarchar(1000) = null,
	@Fax nvarchar(100) = null,
	@Career nvarchar(200) = null,
	@Avatar nvarchar(100) = null,
	@IsActived bit = null,
	@CreatedDate datetime = null,
	@LastModifiedDate datetime = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[Users]
					WHERE
					(
						(@IdUser is null OR [Users].[IdUser] = @IdUser)
						AND (@UserName is null OR [Users].[UserName] like @UserName)
						AND (@Password is null OR [Users].[Password] like @Password)
						AND (@Email is null OR [Users].[Email] like @Email)
						AND (@Fullname is null OR [Users].[Fullname] like @Fullname)
						AND (@Birthdate is null OR [Users].[Birthdate] = @Birthdate)
						AND (@Phone is null OR [Users].[Phone] like @Phone)
						AND (@IdCountry is null OR [Users].[IdCountry] = @IdCountry)
						AND (@Province is null OR [Users].[Province] like @Province)
						AND (@Address is null OR [Users].[Address] like @Address)
						AND (@Fax is null OR [Users].[Fax] like @Fax)
						AND (@Career is null OR [Users].[Career] like @Career)
						AND (@Avatar is null OR [Users].[Avatar] like @Avatar)
						AND (@IsActived is null OR [Users].[IsActived] = @IsActived)
						AND (@CreatedDate is null OR [Users].[CreatedDate] = @CreatedDate)
						AND (@LastModifiedDate is null OR [Users].[LastModifiedDate] = @LastModifiedDate)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[Users].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdUser' and @SortType = 1 THEN [Users].[IdUser] END ASC,
						CASE WHEN @SortBy = 'IdUser' and @SortType = 0 THEN [Users].[IdUser] END DESC,
						CASE WHEN @SortBy = 'UserName' and @SortType = 1 THEN [Users].[UserName] END ASC,
						CASE WHEN @SortBy = 'UserName' and @SortType = 0 THEN [Users].[UserName] END DESC,
						CASE WHEN @SortBy = 'Password' and @SortType = 1 THEN [Users].[Password] END ASC,
						CASE WHEN @SortBy = 'Password' and @SortType = 0 THEN [Users].[Password] END DESC,
						CASE WHEN @SortBy = 'Email' and @SortType = 1 THEN [Users].[Email] END ASC,
						CASE WHEN @SortBy = 'Email' and @SortType = 0 THEN [Users].[Email] END DESC,
						CASE WHEN @SortBy = 'Fullname' and @SortType = 1 THEN [Users].[Fullname] END ASC,
						CASE WHEN @SortBy = 'Fullname' and @SortType = 0 THEN [Users].[Fullname] END DESC,
						CASE WHEN @SortBy = 'Birthdate' and @SortType = 1 THEN [Users].[Birthdate] END ASC,
						CASE WHEN @SortBy = 'Birthdate' and @SortType = 0 THEN [Users].[Birthdate] END DESC,
						CASE WHEN @SortBy = 'Phone' and @SortType = 1 THEN [Users].[Phone] END ASC,
						CASE WHEN @SortBy = 'Phone' and @SortType = 0 THEN [Users].[Phone] END DESC,
						CASE WHEN @SortBy = 'IdCountry' and @SortType = 1 THEN [Users].[IdCountry] END ASC,
						CASE WHEN @SortBy = 'IdCountry' and @SortType = 0 THEN [Users].[IdCountry] END DESC,
						CASE WHEN @SortBy = 'Province' and @SortType = 1 THEN [Users].[Province] END ASC,
						CASE WHEN @SortBy = 'Province' and @SortType = 0 THEN [Users].[Province] END DESC,
						CASE WHEN @SortBy = 'Address' and @SortType = 1 THEN [Users].[Address] END ASC,
						CASE WHEN @SortBy = 'Address' and @SortType = 0 THEN [Users].[Address] END DESC,
						CASE WHEN @SortBy = 'Fax' and @SortType = 1 THEN [Users].[Fax] END ASC,
						CASE WHEN @SortBy = 'Fax' and @SortType = 0 THEN [Users].[Fax] END DESC,
						CASE WHEN @SortBy = 'Career' and @SortType = 1 THEN [Users].[Career] END ASC,
						CASE WHEN @SortBy = 'Career' and @SortType = 0 THEN [Users].[Career] END DESC,
						CASE WHEN @SortBy = 'Avatar' and @SortType = 1 THEN [Users].[Avatar] END ASC,
						CASE WHEN @SortBy = 'Avatar' and @SortType = 0 THEN [Users].[Avatar] END DESC,
						CASE WHEN @SortBy = 'IsActived' and @SortType = 1 THEN [Users].[IsActived] END ASC,
						CASE WHEN @SortBy = 'IsActived' and @SortType = 0 THEN [Users].[IsActived] END DESC,
						CASE WHEN @SortBy = 'CreatedDate' and @SortType = 1 THEN [Users].[CreatedDate] END ASC,
						CASE WHEN @SortBy = 'CreatedDate' and @SortType = 0 THEN [Users].[CreatedDate] END DESC,
						CASE WHEN @SortBy = 'LastModifiedDate' and @SortType = 1 THEN [Users].[LastModifiedDate] END ASC,
						CASE WHEN @SortBy = 'LastModifiedDate' and @SortType = 0 THEN [Users].[LastModifiedDate] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[Users]
			Where 
			(
				(@IdUser is null OR [Users].[IdUser] = @IdUser)
				AND (@UserName is null OR [Users].[UserName] like @UserName)
				AND (@Password is null OR [Users].[Password] like @Password)
				AND (@Email is null OR [Users].[Email] like @Email)
				AND (@Fullname is null OR [Users].[Fullname] like @Fullname)
				AND (@Birthdate is null OR [Users].[Birthdate] = @Birthdate)
				AND (@Phone is null OR [Users].[Phone] like @Phone)
				AND (@IdCountry is null OR [Users].[IdCountry] = @IdCountry)
				AND (@Province is null OR [Users].[Province] like @Province)
				AND (@Address is null OR [Users].[Address] like @Address)
				AND (@Fax is null OR [Users].[Fax] like @Fax)
				AND (@Career is null OR [Users].[Career] like @Career)
				AND (@Avatar is null OR [Users].[Avatar] like @Avatar)
				AND (@IsActived is null OR [Users].[IsActived] = @IsActived)
				AND (@CreatedDate is null OR [Users].[CreatedDate] = @CreatedDate)
				AND (@LastModifiedDate is null OR [Users].[LastModifiedDate] = @LastModifiedDate)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[Users_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[Users_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Users_Insert]
	@IdUser int OUTPUT,
	@UserName nvarchar(100),
	@Password nvarchar(100),
	@Email nvarchar(100),
	@Fullname nvarchar(200),
	@Birthdate datetime,
	@Phone nvarchar(20),
	@IdCountry int,
	@Province nvarchar(100),
	@Address nvarchar(1000),
	@Fax nvarchar(100),
	@Career nvarchar(200),
	@Avatar nvarchar(100),
	@IsActived bit,
	@CreatedDate datetime,
	@LastModifiedDate datetime

AS


INSERT INTO [dbo].[Users] 
(
	[UserName],
	[Password],
	[Email],
	[Fullname],
	[Birthdate],
	[Phone],
	[IdCountry],
	[Province],
	[Address],
	[Fax],
	[Career],
	[Avatar],
	[IsActived],
	[CreatedDate],
	[LastModifiedDate]
)
VALUES 
(
	@UserName,
	@Password,
	@Email,
	@Fullname,
	@Birthdate,
	@Phone,
	@IdCountry,
	@Province,
	@Address,
	@Fax,
	@Career,
	@Avatar,
	@IsActived,
	@CreatedDate,
	@LastModifiedDate
)

SET @IdUser = SCOPE_IDENTITY()

--end [dbo].[Users_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[Users_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Users_Update]
	@IdUser int,
	@UserName nvarchar(100),
	@Password nvarchar(100),
	@Email nvarchar(100),
	@Fullname nvarchar(200),
	@Birthdate datetime,
	@Phone nvarchar(20),
	@IdCountry int,
	@Province nvarchar(100),
	@Address nvarchar(1000),
	@Fax nvarchar(100),
	@Career nvarchar(200),
	@Avatar nvarchar(100),
	@IsActived bit,
	@CreatedDate datetime,
	@LastModifiedDate datetime
AS


UPDATE [dbo].[Users] SET
	[UserName] = @UserName,
	[Password] = @Password,
	[Email] = @Email,
	[Fullname] = @Fullname,
	[Birthdate] = @Birthdate,
	[Phone] = @Phone,
	[IdCountry] = @IdCountry,
	[Province] = @Province,
	[Address] = @Address,
	[Fax] = @Fax,
	[Career] = @Career,
	[Avatar] = @Avatar,
	[IsActived] = @IsActived,
	[CreatedDate] = @CreatedDate,
	[LastModifiedDate] = @LastModifiedDate
WHERE
	[IdUser] = @IdUser

--end [dbo].[Users_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[Users_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[Users_Delete]
	@IdUser int
AS


DELETE FROM [dbo].[Users]
WHERE
(
	[IdUser] = @IdUser
)

--end [dbo].[Users_Delete]
--endregion

GO
--=========================================================================================--

